package com.regulareport.common.utils.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

public class ExcelToJsonConverter2 {

    public static String convertExcelToJson(String filePath) throws IOException {
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new HSSFWorkbook(fis)) {

            ObjectMapper mapper = new ObjectMapper();
            ArrayNode sheetsArray = mapper.createArrayNode();

            // 遍历所有工作表
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                ObjectNode sheetNode = mapper.createObjectNode();
                ArrayNode rowsArray = mapper.createArrayNode();

                sheetNode.put("sheetName", sheet.getSheetName());
                int test =  sheet.getNumMergedRegions();
                System.out.println("TEST  VALUE  IS  ============ " +test);
                // 遍历行
                Iterator<Row> rowIterator = sheet.iterator();
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    ObjectNode rowNode = mapper.createObjectNode();
                    ArrayNode cellsArray = mapper.createArrayNode();

                    // 遍历单元格
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        ObjectNode cellNode = mapper.createObjectNode();
                        // 在单元格信息中添加合并信息
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(i);
                            if (merged.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                                cellNode.put("merged", true);
                                cellNode.put("firstRow", merged.getFirstRow());
                                cellNode.put("lastRow", merged.getLastRow());
                                cellNode.put("firstColumn", merged.getFirstColumn());
                                cellNode.put("lastColumn", merged.getLastColumn());
                            }
                        }

                        // 添加单元格基本信息
                        cellNode.put("columnIndex", cell.getColumnIndex());
                        cellNode.put("rowIndex", cell.getRowIndex());

                        // 添加单元格值
                        switch (cell.getCellType()) {
                            case STRING:
                                cellNode.put("value", cell.getStringCellValue());
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    cellNode.put("value", cell.getDateCellValue().toString());
                                    cellNode.put("type", "date");
                                } else {
                                    cellNode.put("value", cell.getNumericCellValue());
                                    cellNode.put("type", "number");
                                }
                                break;
                            case BOOLEAN:
                                cellNode.put("value", cell.getBooleanCellValue());
                                break;
                            case FORMULA:
                                cellNode.put("value", cell.getCellFormula());
                                cellNode.put("type", "formula");
                                break;
                            default:
                                cellNode.put("value", "");
                        }

                        // 添加单元格样式信息
                        CellStyle style = cell.getCellStyle();
                        ObjectNode styleNode = mapper.createObjectNode();

                        // 字体信息
                        Font font = workbook.getFontAt(style.getFontIndex());
                        ObjectNode fontNode = mapper.createObjectNode();
                        fontNode.put("bold", font.getBold());
                        fontNode.put("italic", font.getItalic());
                        fontNode.put("fontName", font.getFontName());
                        fontNode.put("fontHeight", font.getFontHeight());
                        fontNode.put("color", font.getColor());
                        styleNode.set("font", fontNode);

                        // 背景色
//                        styleNode.put("fillForegroundColor", style.getFillForegroundColorColor() != null
//                                ? style.getFillForegroundColorColor().getRGB() : -1);
                        styleNode.put("fillPattern", style.getFillPattern().name());

                        // 边框
                        styleNode.put("borderTop", style.getBorderTop().name());
                        styleNode.put("borderBottom", style.getBorderBottom().name());
                        styleNode.put("borderLeft", style.getBorderLeft().name());
                        styleNode.put("borderRight", style.getBorderRight().name());

                        // 对齐方式
                        styleNode.put("alignment", style.getAlignment().name());
                        styleNode.put("verticalAlignment", style.getVerticalAlignment().name());

                        cellNode.set("style", styleNode);
                        cellsArray.add(cellNode);
                    }

                    rowNode.set("cells", cellsArray);
                    rowsArray.add(rowNode);
                }

                sheetNode.set("rows", rowsArray);
                sheetsArray.add(sheetNode);
            }

            return mapper.writerWithDefaultPrettyPrinter().writeValueAsString(sheetsArray);
        }
    }

    public static void main(String[] args) {
        try {
            String json = convertExcelToJson("D:\\temp\\document\\1104\\GF0401-231.xls");
            System.out.println(json);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}